Redshiftで値の途中に引用符等を含むデータを取り込む

RedshiftにS3のCSVテキストファイルからデータをCOPYする際、データに引用符などの文字が入っているとエラーになってしまう問題の解決方法を紹介しています。

#ビッグデータ

#Amazon Redshift

#AWS

平野重利

2018.10.10

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

`"`の入った文字列でエラーになった

Redshiftでs3にあるCSVテキスト¹ファイルからデータをCOPYする際に、データに"（ダブルクォーテーション）が含まれているとエラーになってしまっていたので、その対処法と、COPYのパラメータについて調べてみました。

失敗した例

S3に置いてあるファイルに以下のようなレコードがあるとき、

0001|aaa "bbb" ccc

失敗していたCOPYは以下のようなものでした。

COPY quotation_test
FROM 's3://xxxxxxxxx/xxxx/xxxx'
ACCESS_KEY_ID '〜〜〜〜'
SECRET_ACCESS_KEY '〜〜〜〜'
SESSION_TOKEN '〜〜〜〜'
FORMAT AS CSV
DELIMITER '|'
;

FORMAT AS CSVで、デリミタとして|を指定しています。失敗の原因を調べるためにSTL_LOAD_ERRORSを見てみると

select * from stl_load_errors;
==> Invalid quote formatting for CSV

と出ていました。どうやら"が原因のようです。

失敗した原因

デリミタは|に設定しているので、"はなんら影響を及ぼさないように見えますが、実はFORMAT AS CSVを指定している時にはQUOTEというパラメータのデフォルト値として"が使われています。

`QUOTE`パラメータとは

QUOTEパラメータはFORMAT AS CSVが設定された時に同時に設定されるパラメータで、デフォルト値は"です。 QUOTEとして"が設定されている時の挙動は以下のようになります。

各カラムの値が"で囲まれている場合、それを除去する
カラムの値を囲う用途以外の"が発見されたらエラーとする

この2番目がまさに今回のエラーの原因となっていました。 QUOTEに設定された文字は、値を囲うための専用文字として確保されてしまうようです。なお、ややこしいのですが、 値が"で囲まれていない分には全く構わない　ので、全ての値を囲う必要はありません。²

対処法１：元データを編集

FORMAT AS CSVを指定した時にも、"をエスケープする方法があるので、それを使います。

その値全体を"で囲う
""（ダブルクォーテーション２つ）のように、QUOTE文字を２つ並べる

とすればOKです。ポイントは、値の中に"を登場させたい場合は、 必ず、その値全体が"で囲われていなければならない という点です。ただ""に直しても相変わらずエラーになってしまうので注意が必要です。

元データを編集してCOPY可能にした例

0001|"aaa ""bbb"" ccc"

COPY quotation_test
FROM 's3://xxxxxxxxx/xxxx/xxxx'
ACCESS_KEY_ID '〜〜〜〜'
SECRET_ACCESS_KEY '〜〜〜〜'
SESSION_TOKEN '〜〜〜〜'
FORMAT AS CSV
DELIMITER '|'
;

id	message
0001	aaa "bbb" ccc

これで無事COPYが成功します。

対処法２：COPYパラメータで対処

上記では元データを編集しましたが、現実には元データは編集できなかったり、もう大量にデータが溜まっていてそれを全て修正するのは大変（手間というより、データを書き換えてしまう恐れなど）なことがほとんどかと思います。なので、COPYのパラメータを修正してこのデータに対処します。

`FORMAT`を指定しない

実は対処法はとても簡単で、FORMAT AS CSVの指定をなくせば大丈夫です。 "に特別な意味を与えていたQUOTEパラメータの設定は、FORMAT AS CSVに付随して有効になったものですので、それを解除します。 FORMATを指定しない場合の挙動は以下です。

DELIMITER文字をカラム区切りとする
DELIMITER文字のデフォルト値は|

デリミタ文字を見つけて、そこで分割するだけ。非常にシンプルです。

COPYのパラメータを編集してCOPY可能にした例

0001|aaa "bbb" ccc

COPY quotation_test
FROM 's3://xxxxxxxxx/xxxx/xxxx'
ACCESS_KEY_ID '〜〜〜〜'
SECRET_ACCESS_KEY '〜〜〜〜'
SESSION_TOKEN '〜〜〜〜'
DELIMITER '|' --デフォルト値なので実は不要
;

id	message
0001	aaa "bbb" ccc

単純に|で区切っただけですので、単純明快です。

`FORMAT`指定ナシに出来ないか考えよう

FORMAT AS CSVを指定するとQUOTEが設定され、上記のようなハマりポイントになりがちです。しかし、QUOTEの仕様が少し分かりづらいながらも、FORMAT AS CSVが指定されているケースは少なくないように思います。その原因は「FORMATに何か指定しないといけないのではないか？」というのが主な原因だと思うのですが、いかがでしょうか？

もし「デリミタを指定して、それでカラムを区切りたいだけ」と考えているなら、実は「FORMATを指定せずDELMIITERを指定する」が正解です。 CSVという名前がいかにもそれっぽいのですが、QUOTEのことを忘れると痛い目を見ます。

もちろん、QUOTEが有効になることで想定外データを検知しやすくなるなどの側面もあると思いますので、使い勝手とはトレードオフですが、何にしてもCOPYのパラメータなどの仕様はしっかり理解しておくに越したことはありません。